from sqlalchemy import text

class dbTransactionCurd:
    @staticmethod
    def dbListJon(db,sql, parm=None):
        try:
            rest = db.session.execute(text(sql), parm)
        except Exception as e:
            return None, repr(e)
        # 游标对象的description属性得到由字段和其他值组成的元组，格式：((字段1，...),(字段2，...)...)
        # 通过列表推导式，遍历元组，取遍历后的元组的第一个索引的值组成一个有字段组成的列表
        field_li = [k[0].lower() for k in rest.cursor.description]
        # 所有数据
        try:
            data_all = rest.fetchall()  # 查询所有数据，fetchall()或fetchone()要放到description后面，否则会报错
        except Exception as e:
            db.session.rollback()
            db.session.close()
            return None, repr(e)
        result_list = list()
        for data in data_all:
            result = dict(zip(field_li, data))
            result_list.append(result)
        return result_list, None

    @staticmethod
    def dbOneJson(db,sql, parm=None):
        try:
            rest = db.session.execute(text(sql), parm)
        except Exception as e:
            db.session.rollback()
            db.session.close()
            return None, repr(e)
        # 游标对象的description属性得到由字段和其他值组成的元组，格式：((字段1，...),(字段2，...)...)
        # 通过列表推导式，遍历元组，取遍历后的元组的第一个索引的值组成一个有字段组成的列表
        field_li = [k[0].lower() for k in rest.cursor.description]
        # 所有数据
        try:
            data = rest.fetchone()
        except Exception as e:
            db.session.close()
            return None, repr(e)
        if data is None:
            return None, None
        result = dict(zip(field_li, data))
        return result, None

    @staticmethod
    def dbCPD(db,sql,parm=None):
        try:
            db.session.execute(text(sql),parm)
        except Exception as e:
            db.session.rollback()
            db.session.close()
            return None,repr(e)
        return None,None